/**
 * Collaborative Story Development Web Application (CSDApp) Copyright Anton
 * Strack 2014
 *
 * This file is part of Collaborative Story Development Application (CSDApp).
 *
 * CSDApp is free software: you can redistribute it and/or modify it under the
 * terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or any later version.
 *
 * CSDApp is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * CSDApp. If not, see <http://www.gnu.org/licenses/>.
 *
 */
package csdwa;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * This class is a special type of dao that primarily maps two tables together
 * that share a many to many relationship. This class uses the dao's of the
 * respective tables to select the values from those tables and it also contains
 * it's own dao functions for the mapping table itself.
 *
 *
 * @author Anton Strack
 */
public class RelatedMapperDao extends DaoSupport implements CsdwaDao<RelatedMap> {

    /**
     * The name of the relationship mapping table.
     */
    protected String mappingTableName;
    /**
     * the column name that makes up part of the key. The column A that
     * corresponds to the postA methods in this class.
     */
    protected String relatedAIdColumnName;
    /**
     * the column name that makes up part of the key. The column B that
     * corresponds to the postB methods in this class.
     */
    protected String relatedBIdColumnName;
    /**
     * The specific type of the relationship between postA and postB
     */
    protected String relationshipTypeIdColumnName;
    /**
     * The resource type of postA
     */
    protected int relatedAResourceTypeId;
    /**
     * The resource type of postB
     */
    protected int relatedBResourceTypeId;
    /**
     * A list of the beans that make up the mapping between the two other
     * tables.
     */
    private List<RelatedMap> beans;
    /**
     * The category dao that maps to the post
     */
    private CsdwaDao<? extends Post> relatedADao;
    /**
     * The dao object for a post
     */
    private CsdwaDao<? extends Post> relatedBDao;

    /**
     * Construct the RoleDao
     *
     * @param conn the pre-configured connection to the database.
     */
    public RelatedMapperDao(Connection conn, int relatedAResourceTypeId, int relatedBResourceTypeId) {
        super(conn);
        this.beans = new ArrayList<RelatedMap>();
        DaoFactory df = new DaoFactory(conn);
        this.relatedADao = df.getDao(relatedAResourceTypeId);
        this.relatedBDao = df.getDao(relatedBResourceTypeId);
        this.determineTableAndColumn(relatedAResourceTypeId, relatedBResourceTypeId);
        this.restoreQueryMarkers();
    }

    /**
     * This method determines the tables and columns for the particular related
     * resource combination.
     *
     * @param relatedAResourceTypeId the resource type id of the resource in
     * column A - the first column
     * @param relatedBResourceTypeId the resource type id of the resource in
     * column B - the second column
     */
    protected void determineTableAndColumn(int relatedAResourceTypeId, int relatedBResourceTypeId) {
        this.relatedAResourceTypeId = relatedAResourceTypeId;
        this.relatedBResourceTypeId = relatedBResourceTypeId;
        String combinedId = relatedAResourceTypeId + "" + relatedBResourceTypeId;
        if (relatedAResourceTypeId > relatedBResourceTypeId) {
            // the switch statement just lists combination with lowest number being first, so rearrange the numbers to match that pattern.
            // externally there shouldn't be any difference.
            combinedId = relatedBResourceTypeId + "" + relatedAResourceTypeId;
        }
        int mapId = Integer.parseInt(combinedId);
        switch (mapId) {
            case 33: // plotplot
            {
                this.mappingTableName = "plothierarchy";
                this.relationshipTypeIdColumnName = mappingTableName + ".RelationshipTypeID";
                this.relatedAIdColumnName = mappingTableName + ".PlotA_ID";
                this.relatedBIdColumnName = mappingTableName + ".PlotB_ID";
                break;
            }
            case 34: // plotcharacter
            {
                this.mappingTableName = "plotactor";
                this.relationshipTypeIdColumnName = mappingTableName + ".RelationshipTypeID";
                this.relatedAIdColumnName = mappingTableName + ".ActorID";
                this.relatedBIdColumnName = mappingTableName + ".PlotID";
                break;
            }
            case 35: // plotdialog
            {
                this.mappingTableName = "plotdialog";
                this.relationshipTypeIdColumnName = mappingTableName + ".RelationshipTypeID";
                this.relatedAIdColumnName = mappingTableName + ".DialogID";
                this.relatedBIdColumnName = mappingTableName + ".PlotID";
                break;
            }
            case 36: // plotlocation
            {
                this.mappingTableName = "plotlocation";
                this.relationshipTypeIdColumnName = mappingTableName + ".RelationshipTypeID";
                this.relatedAIdColumnName = mappingTableName + ".LocationID";
                this.relatedBIdColumnName = mappingTableName + ".PlotID";
                break;
            }
            case 44: // charactercharacter
            {
                this.mappingTableName = "actorrelationship";
                this.relationshipTypeIdColumnName = mappingTableName + ".RelationshipTypeID";
                this.relatedAIdColumnName = mappingTableName + ".ActorA_ID";
                this.relatedBIdColumnName = mappingTableName + ".ActorB_ID";
                break;
            }
            case 45: // characterdialog
            {
                this.mappingTableName = "dialogactor";
                this.relationshipTypeIdColumnName = mappingTableName + ".RelationshipTypeID";
                this.relatedAIdColumnName = mappingTableName + ".ActorID";
                this.relatedBIdColumnName = mappingTableName + ".DialogID";
                break;
            }
        }
    }

    /**
     * Select all the beans from the bean table
     *
     * @return the row count indicating if 0 or more beans were retrieved from
     * the database.
     */
    @Override
    public int selectBeans() {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "1=1");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param postAId the id of the category that partially makes up the row's
     * id
     * @param postBId the id of the post that partially makes up the row's id
     * @return 1 or 0, indicating a bean was retrieved or not.
     */
    @Override
    public int selectBeanById(int id) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, this.mappingTableName + ".ID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, id);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param postAId the id of the category that partially makes up the row's
     * id
     * @param postBId the id of the post that partially makes up the row's id
     * @return 1 or 0, indicating a bean was retrieved or not.
     */
    public int selectBeanById(int postAId, int postBId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, this.relatedAIdColumnName + " = ? AND " + this.relatedBIdColumnName + " = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, postAId);
            this.ps.setInt(2, postBId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param postAId the id number of the bean to select.
     * @return 1 or 0, indicating a bean was retrieved or not.
     */
    public int selectMappingsByPostAId(int postAId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, this.relatedAIdColumnName + " = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, postAId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param postBId the id number of the bean to select.
     * @return 1 or 0, indicating a bean was retrieved or not.
     */
    public int selectMappingsByPostBId(int postBId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, this.relatedBIdColumnName + " = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, postBId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Save a bean to the database
     *
     * @param bean the bean to insert. Since this is a mapping table, both
     * foreign keys are required to be set in the bean.
     * @return 1 if success or 0 on failure.
     */
    @Override
    public int insertBean(RelatedMap bean) {
        int result = 0;
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_COLUMNS);
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.usingQueryMarkers.add(QueryMarker.INSERT_VALUES);
        /*
         * HSQLDB cannot properly handle insert statements that have table identifiesr in the form of table.columnName so we need to remove the table name for inserts.
         */
        this.queryMarkers.put(QueryMarker.CORE_COLUMNS, this.prepareInsertColumns(this.getMarker(QueryMarker.CORE_COLUMNS)));
        try {
            this.ps = this.getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, INSERT_QUERY));
            this.ps.setNull(1, java.sql.Types.NULL);
            this.ps.setInt(2, bean.getPostAId());
            this.ps.setInt(3, bean.getPostBId());
            this.ps.setInt(4, bean.getRelationshipTypeId());
            result = this.ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Update a bean in the database
     *
     * @param bean the bean to update.
     * @return 1 or 0 indicating if the bean was updated or not.
     */
    @Override
    public int updateBean(RelatedMap bean) {
        throw new UnsupportedOperationException("Category Post table cannot be updated.");
    }

    /**
     * Deletes the bean from the database
     *
     * @param bean the bean to delete
     * @return 1 or 0 indicating if the bean was deleted or not.
     */
    @Override
    public int deleteBean(RelatedMap bean) {
        int result = 0;
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_CONDITIONS);
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.setMarker(QueryMarker.CORE_CONDITIONS, this.mappingTableName + ".ID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, DELETE_QUERY));
            this.ps.setInt(1, bean.getId());
            result = this.ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Get a list of beans
     *
     * @return a list of bean objects
     */
    @Override
    public List<RelatedMap> getBeans() {
        return new ArrayList<RelatedMap>(this.beans);
    }

    /**
     * Add a bean in the dao's bean list.
     *
     * @param bean the bean to add or update
     */
    @Override
    public void addBeanToDao(RelatedMap bean) {
        this.beans.add(bean);
    }

    /**
     * Reset the query markers to their default values for the dao.
     */
    @Override
    public void restoreQueryMarkers() {
        this.setMarker(QueryMarker.CORE_TABLE, this.mappingTableName);
        this.setMarker(QueryMarker.CORE_COLUMNS, this.mappingTableName + ".ID, " + this.relatedAIdColumnName + ", " + this.relatedBIdColumnName + ", " + this.relationshipTypeIdColumnName);
        this.setMarker(QueryMarker.CORE_CONDITIONS, this.relatedAIdColumnName + " = ? AND " + this.relatedBIdColumnName + " = ?");
        this.setMarker(QueryMarker.ORDER_BY_COLUMN, this.mappingTableName + ".ID");
        this.setMarker(QueryMarker.ORDER_BY_MODE, "ASC");
        this.setMarker(QueryMarker.LIMIT, "");
        this.setMarker(QueryMarker.INSERT_VALUES, "?, ?, ?, ?");
        this.setMarker(QueryMarker.UPDATE_COLUMNS_VALUES, "");
        // special type of derived values
        if (this.relatedADao != null && this.relatedBDao != null) {
            String postATable = this.relatedADao.getQueryMarkerValue(QueryMarker.CORE_TABLE);
            String postBTable = this.relatedBDao.getQueryMarkerValue(QueryMarker.CORE_TABLE);

            this.setMarker(QueryMarker.RELATED_COLUMNS, ", aTable.Name AS PostAName, bTable.Name AS PostBName, relationshiptype.Name AS RelationshipTypeName, aResourceType.Name AS PostATypeName, bResourceType.Name AS PostBTypeName");
            this.setMarker(QueryMarker.RELATED_CONDITIONS, " AND relationshiptype.ID = " + this.mappingTableName + ".RelationshipTypeId AND aTable." + this.relatedADao.getQueryMarkerValue(QueryMarker.PRIMARY_KEY) + " = " + this.relatedAIdColumnName + " AND bTable." + this.relatedBDao.getQueryMarkerValue(QueryMarker.PRIMARY_KEY) + " = " + this.relatedBIdColumnName + " AND aTable.ResourceTypeID = aResourceType.ID AND bTable.ResourceTypeID = bResourceType.ID");
            this.setMarker(QueryMarker.RELATED_TABLES, ", " + postATable + " AS aTable, " + postBTable + " AS bTable, relationshiptype, resourcetype AS aResourceType, resourcetype AS bResourceType");
        }

    }

    /**
     * Execute the select query and place the results in beans list.
     *
     * @param ps the fully prepared PreparedStatement
     * @return the row count from the select statement
     */
    @Override
    protected int executeSelectQuery(PreparedStatement ps) {
        int result = 0;
        ResultSet rs;
        try {
            rs = ps.executeQuery();
            while (rs.next()) {
                RelatedMap bean = new RelatedMap();
                bean.setId(rs.getInt(this.mappingTableName + ".ID"));
                bean.setPostAId(rs.getInt(this.relatedAIdColumnName));
                bean.setPostBId(rs.getInt(this.relatedBIdColumnName));
                bean.setRelationshipTypeId(rs.getInt(this.relationshipTypeIdColumnName));
                if (this.USE_RELATED_MARKERS == true) {
                    bean.setDerivedValue("PostAName", rs.getString("PostAName"));
                    bean.setDerivedValue("PostBName", rs.getString("PostBName"));
                    bean.setDerivedValue("RelationshipTypeName", rs.getString("RelationshipTypeName"));
                    bean.setDerivedValue("PostBTypeName", rs.getString("PostBTypeName"));
                    bean.setDerivedValue("PostATypeName", rs.getString("PostATypeName"));
                }
                this.beans.add(bean);
                result++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(RelatedMapperDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /* mapping related methods */
    /**
     * This method selects all posts that are identified in the postBId column
     * of the mapping table according to the id value of the matching postAId
     * column.
     *
     * @param APostId id of the postA column of the relationship
     * @param useRelatedMarkers if the dao of postA should use related markers
     * or not.
     * @return the result count of the select statement.
     */
    public int selectRelatedBPostsByAPostId(int APostId, boolean useRelatedMarkers) {
        int result = 0;

        if (this.relatedBDao != null) {
            this.relatedBDao.setUseRelatedMarkers(useRelatedMarkers);
            String columns = this.relatedBIdColumnName + ", relationshiptype.Name AS RelationshipTypeName";
            String tables = this.mappingTableName + ", relationshiptype";
            StringBuilder sbCondition = new StringBuilder();
            sbCondition.append(this.relatedBDao.getQueryMarkerValue(QueryMarker.CORE_TABLE)).append(".").append(this.relatedBDao.getQueryMarkerValue(QueryMarker.PRIMARY_KEY)).append(" = ").append(this.relatedBIdColumnName).append(" AND ").append(this.relatedAIdColumnName).append(" = ").append(APostId).append(" AND ").append(".").append(this.relationshipTypeIdColumnName).append(" = relationshiptype.ID");
            this.relatedBDao.addDerivedValue(columns, tables, sbCondition.toString());
            this.relatedBDao.setQueryMarkerValue(QueryMarker.ORDER_BY_COLUMN, "relationshiptype.Name");
            this.relatedBDao.setQueryMarkerValue(QueryMarker.ORDER_BY_MODE, "ASC");
            result = this.relatedBDao.selectBeans();
            System.out.println("selectPostsByCategoryId result is:" + result);
        }
        return result;

    }

    /**
     * This method selects all posts that are identified in the postAId column
     * of the mapping table according to the id value of the matching postBId
     * column.
     *
     * @param BPostId id of the postB column of the relationship
     * @param useRelatedMarkers if the dao of postA should use related markers
     * or not.
     * @return the result count of the select statement.
     */
    public int selectRelatedAPostsByBPostId(int BPostId, boolean useRelatedMarkers) {
        int result = 0;
        if (this.relatedADao != null) {
            this.relatedADao.setUseRelatedMarkers(useRelatedMarkers);
            String columns = this.relatedBIdColumnName + ", relationshiptype.Name AS RelationshipTypeName";
            String tables = this.mappingTableName + ", relationshiptype";
            StringBuilder sbCondition = new StringBuilder();
            sbCondition.append(this.relatedADao.getQueryMarkerValue(QueryMarker.CORE_TABLE)).append(".").append(this.relatedADao.getQueryMarkerValue(QueryMarker.PRIMARY_KEY)).append(" = ").append(this.relatedAIdColumnName).append(" AND ").append(this.relatedBIdColumnName).append(" = ").append(BPostId).append(" AND ").append(".").append(this.relationshipTypeIdColumnName).append(" = relationshiptype.ID");
            this.relatedADao.addDerivedValue(columns, tables, sbCondition.toString());
            this.relatedADao.setQueryMarkerValue(QueryMarker.ORDER_BY_COLUMN, "relationshiptype.Name");
            this.relatedADao.setQueryMarkerValue(QueryMarker.ORDER_BY_MODE, "ASC");
            result = this.relatedADao.selectBeans();
            System.out.println("selectCategoriesByPostId result is:" + result);
        }
        return result;
    }

    /**
     * Get the postA dao object.
     *
     * @return the postA dao object or null
     */
    public CsdwaDao<? extends Post> getPostADao() {
        return this.relatedADao;
    }

    /**
     * Get the postB dao object.
     *
     * @return the postB dao object or null
     */
    public CsdwaDao<? extends Post> getPostBDao() {
        return this.relatedBDao;
    }

    /**
     * @return the relatedAResourceTypeId
     */
    public int getRelatedAResourceTypeId() {
        return relatedAResourceTypeId;
    }

    /**
     * @param relatedAResourceTypeId the relatedAResourceTypeId to set
     */
    public void setRelatedAResourceTypeId(int relatedAResourceTypeId) {
        this.relatedAResourceTypeId = relatedAResourceTypeId;
    }

    /**
     * @return the relatedBResourceTypeId
     */
    public int getRelatedBResourceTypeId() {
        return relatedBResourceTypeId;
    }

    /**
     * @param relatedBResourceTypeId the relatedBResourceTypeId to set
     */
    public void setRelatedBResourceTypeId(int relatedBResourceTypeId) {
        this.relatedBResourceTypeId = relatedBResourceTypeId;
    }

    /**
     * This method extracts the derived values from the result set as indicated
     * by the derived columns query marker. This method then adds the values to
     * the bean's derived value map using the column name as the key.
     *
     * @param bean the bean being processed in the select query
     * @param rs the result set from the select query
     */
    public void processDerivedValues(RelatedMap bean, ResultSet rs) {
        if (this.getMarker(QueryMarker.DERIVED_COLUMNS).length() > 0) {
            String[] cols = this.splitStringByCommas(this.getMarker(QueryMarker.DERIVED_COLUMNS));

            for (String column : cols) {
                try {
                    /**
                     * to avoid the jsps needing to know tables, many derived
                     * columns us AS to create aliases and so the result set
                     * stores the columns by those aliases. Thus need to convert
                     * the full column name; tableName.columnName AS aliasName
                     * into just aliasName for retrieving the value.
                     */
                    int asIndex = column.indexOf(" AS ");
                    if (asIndex >= 0) {
                        int subStringIndex = asIndex + 4; // want to start after AS + space.
                        column = column.substring(subStringIndex);// return the column name after AS.
                    }
                    bean.setDerivedValue(column, rs.getString(column));
                } catch (SQLException ex) {
                    Logger.getLogger(DaoSupport.class.getName()).log(Level.SEVERE, null, ex);
                    ex.printStackTrace();
                } catch (Exception ex) {
                    Logger.getLogger(DaoSupport.class.getName()).log(Level.SEVERE, null, ex);
                    ex.printStackTrace();
                }
            }
        }
    }

    /**
     * Clear the internal bean list.
     */
    @Override
    public void clearDaoBeans() {
        this.beans.clear();
    }

    /**
     * Select the related map bean by its id value.
     *
     * @param id
     * @return the bean or null if none exists.
     */
    @Override
    public RelatedMap getBeanById(int id) {
        for (RelatedMap rm : this.beans) {
            if (rm.getId() == id) {
                return rm;
            }
        }
        return null;
    }

    /**
     * Get the first or only relatedMap bean.
     *
     * @return the related map bean.
     */
    @Override
    public RelatedMap getBean() {
        return this.beans.get(0);
    }
}
